USE [CodeProject]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ksEmployee]') AND type in (N'U'))
    DROP TABLE [dbo].[ksEmployee]
GO
CREATE TABLE [dbo].[ksEmployee] (
[ID] int  NOT NULL IDENTITY 
, [Name] varchar(100)  NOT NULL  
, [DOB] date  NOT NULL  
)
GO

ALTER TABLE [dbo].[ksEmployee] ADD CONSTRAINT [ksEmployee_PK] PRIMARY KEY CLUSTERED (
[ID]
)
GO
GO


GO
CREATE PROCEDURE [sp_ksEmployeeFetch]
		@ID int
AS
BEGIN

SELECT
	[Name], [DOB]
FROM ksEmployee (NOLOCK)
 WHERE ID = @ID
END


GO
CREATE PROCEDURE [sp_ksEmployeeFetchAll]
AS
BEGIN

SELECT
	emp.[Name], emp.[DOB], dept.[Name] [Department]
FROM ksEmployee emp(NOLOCK)
INNER JOIN ksDepartmentAssg assg ON
emp.ID = assg.refEmployeeID
INNER JOIN ksDepartment dept ON
dept.ID = assg.refDepartmentID


END


GO
CREATE PROCEDURE [sp_ksEmployeeUpdate]
	@ID int,
	@Name varchar(100),
	@DOB dateAS
BEGIN

UPDATE ksEmployee SET
	[Name] = @Name, 
	[DOB] = @DOB
 WHERE ID = @ID
END


GO
CREATE PROCEDURE [sp_ksEmployeeInsert]
	@Name varchar(100),
	@DOB date,
	@InsertID int OUTPUT

AS
BEGIN

INSERT INTO ksEmployee (Name, DOB)
VALUES (
	@Name,
	@DOB
)
SELECT @InsertID = SCOPE_IDENTITY()

END



GO
CREATE PROCEDURE [sp_ksEmployeeDelete]
		@ID int
AS
BEGIN

DELETE FROM ksEmployee 
 WHERE ID = @ID
END



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ksDepartment]') AND type in (N'U'))
    DROP TABLE [dbo].[ksDepartment]
GO
CREATE TABLE [dbo].[ksDepartment] (
[ID] int  NOT NULL  IDENTITY 
, [Name] varchar(50)  NOT NULL  
)
GO

ALTER TABLE [dbo].[ksDepartment] ADD CONSTRAINT [ksDepartment_PK] PRIMARY KEY CLUSTERED (
[ID]
)
GO
GO


GO
CREATE PROCEDURE [sp_ksDepartmentFetch]
		@ID int
AS
BEGIN

SELECT
	[Name]
FROM ksDepartment (NOLOCK)
 WHERE ID = @ID
END


GO
CREATE PROCEDURE [sp_ksDepartmentFetchAll]
AS
BEGIN

SELECT
	[Name]
FROM ksDepartment (NOLOCK)

END


GO
CREATE PROCEDURE [sp_ksDepartmentUpdate]
	@ID int,
	@Name varchar(50)AS
BEGIN

UPDATE ksDepartment SET
	[Name] = @Name
 WHERE ID = @ID
END


GO
ALTER PROCEDURE [sp_ksDepartmentInsert]
	@Name varchar(50),
	@InsertID INT OUTPUT
AS
BEGIN

INSERT INTO ksDepartment (Name)
VALUES (
	@Name
)
SELECT @InsertID = SCOPE_IDENTITY()

END



GO
CREATE PROCEDURE [sp_ksDepartmentDelete]
		@ID int
AS
BEGIN

DELETE FROM ksDepartment 
 WHERE ID = @ID
END



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ksDepartmentAssg]') AND type in (N'U'))
    DROP TABLE [dbo].[ksDepartmentAssg]
GO
CREATE TABLE [dbo].[ksDepartmentAssg] (
[ID] int  NOT NULL IDENTITY
, [refEmployeeID] int  NOT NULL  
, [refDepartmentID] int  NOT NULL  
)
GO

ALTER TABLE [dbo].[ksDepartmentAssg] ADD CONSTRAINT [ksDepartmentAssg_PK] PRIMARY KEY CLUSTERED (
[ID]
)
GO
GO


GO
CREATE PROCEDURE [sp_ksDepartmentAssgFetch]
		@ID int
AS
BEGIN

SELECT
	[refEmployeeID], [refDepartmentID]
FROM ksDepartmentAssg (NOLOCK)
 WHERE ID = @ID
END


GO
CREATE PROCEDURE [sp_ksDepartmentAssgFetchAll]
AS
BEGIN

SELECT
	[refEmployeeID], [refDepartmentID]
FROM ksDepartmentAssg (NOLOCK)

END


GO
CREATE PROCEDURE [sp_ksDepartmentAssgUpdate]
	@ID int,
	@refEmployeeID int,
	@refDepartmentID intAS
BEGIN

UPDATE ksDepartmentAssg SET
	[refEmployeeID] = @refEmployeeID, 
	[refDepartmentID] = @refDepartmentID
 WHERE ID = @ID
END


GO
CREATE PROCEDURE [sp_ksDepartmentAssgInsert]
	@refEmployeeID int,
	@refDepartmentID int,
	@InsertID int OUTPUT
AS
BEGIN

INSERT INTO ksDepartmentAssg (refEmployeeID, refDepartmentID)
VALUES (
	@refEmployeeID,
	@refDepartmentID
)
SELECT @InsertID = SCOPE_IDENTITY()

END



GO
CREATE PROCEDURE [sp_ksDepartmentAssgDelete]
		@ID int
AS
BEGIN

DELETE FROM ksDepartmentAssg 
 WHERE ID = @ID
END



ALTER TABLE [dbo].[ksEmployee] WITH CHECK ADD CONSTRAINT [ksDepartmentAssg_ksEmployee_FK1] FOREIGN KEY (
[ID]
)
REFERENCES [dbo].[ksDepartmentAssg] (
[refEmployeeID]
)
GO

ALTER TABLE [dbo].[ksDepartment] WITH CHECK ADD CONSTRAINT [ksDepartmentAssg_ksDepartment_FK1] FOREIGN KEY (
[ID]
)
REFERENCES [dbo].[ksDepartmentAssg] (
[refDepartmentID]
)
GO

GO

/*
THE FOLLOWING PROCEDURES ALTER THE DATABASE FOR OUR NEW REQUIREMENTS


alter table ksEmployee
add first_name varchar(100) NULL

alter table ksEmployee
add last_name varchar(100) NULL

update emp
	set emp.first_name = SUBSTRING(emp.name, 0, CHARINDEX(emp.name, ' ')),
		emp.last_name = SUBSTRING(emp.name, CHARINDEX(emp.name, ' '), 99)
	from ksEmployee emp
	
ALTER PROCEDURE [sp_ksEmployeeFetch]
		@ID int
AS
BEGIN

SELECT
	[first_name] + ' ' + [last_name] [Name], [DOB]
FROM ksEmployee (NOLOCK)
 WHERE ID = @ID
END

ALTER PROCEDURE [sp_ksEmployeeInsert]
	@FirstName varchar(100),
	@LastName varchar(100),
	@DOB date,
	@InsertID int OUTPUT

AS
BEGIN

INSERT INTO ksEmployee (first_name, last_name, DOB)
VALUES (
	@FirstName,
	@LastName,
	@DOB
)
SELECT @InsertID = SCOPE_IDENTITY()

END

GO
ALTER PROCEDURE [sp_ksEmployeeFetchAll]
AS
BEGIN

SELECT
	emp.[first_name] + ' ' + emp.[last_name] [Name], emp.[DOB], dept.[Name] [Department]
FROM ksEmployee emp(NOLOCK)
INNER JOIN ksDepartmentAssg assg ON
emp.ID = assg.refEmployeeID
INNER JOIN ksDepartment dept ON
dept.ID = assg.refDepartmentID
END	

*/
